package com.chuangke.common.sqlparser;

import cn.hutool.core.util.StrUtil;
import com.chuangke.common.exception.ChuangkeException;
import com.chuangke.common.utils.CamelUtil;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.HexValue;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.delete.Delete;
import net.sf.jsqlparser.statement.insert.Insert;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.update.Update;
import net.sf.jsqlparser.util.SelectUtils;
import org.apache.commons.collections.CollectionUtils;

import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

public class SqlUtils {

	public static void main(String[] args) {
//		System.out.println(getSelectSql("sys_user", "a", "b"));

		CbExp cbe1 = new CbExp();
		cbe1.setQcChildren(Arrays.asList(new QcExp("userId", "LIKE", "zhangsan")));
		Map<String, Object> params = new HashMap<>();

		System.out.println(parseCbExp(cbe1, params));
		System.out.println(params);

		System.out.println("----------------------------------");

		CbExp cbe2 = new CbExp();
		cbe2.setOperator("AND");
		cbe2.setQcChildren(
				Arrays.asList(new QcExp("userId", "LIKE", "3420032"), new QcExp("userName", "LIKE", "zhangsan")));
		params = new HashMap<>();

		System.out.println(parseCbExp(cbe2, params));
		System.out.println(params);

		System.out.println("----------------------------------");

		CbExp cbe3 = new CbExp();
		cbe3.setOperator("OR");
		cbe3.setCbChildren(Arrays.asList(cbe1, cbe2));
		System.out.println(parseCbExp(cbe3, params));
		System.out.println(params);

	}

	public static String getBatchInsertSql(String table, String... columns) {
		StringBuilder insert = new StringBuilder();
		insert.append("<insert id=\"insert\" >");
		insert.append("INSERT INTO ").append(table);
		insert.append("(");
		for (int i = 0; i < columns.length; i++) {
			insert.append(columns[i]);
			if (i < columns.length - 1) {
				insert.append(",");
			}
		}
		insert.append(")");
		insert.append(" VALUES ");
		insert.append("<foreach collection =\"list\" item=\"item\" index=\"index\" separator =\",\">");
		insert.append(" (");
		for (int i = 0; i < columns.length; i++) {
			insert.append("#{item.").append(CamelUtil.camelize(columns[i])).append("}");
			if (i < columns.length - 1) {
				insert.append(",");
			}
		}
		insert.append(") ");
		insert.append("</foreach >");
		insert.append("</insert>");
		return insert.toString();
	}

	public static String getInsertSql1(String table, String... columns) {
		List<String> placeholderList = Arrays.stream(columns).map(col -> "?").collect(Collectors.toList());

		StringBuilder insert = new StringBuilder();
		insert.append("INSERT INTO ").append(table);
		insert.append("(");
		insert.append(String.join(",", columns));
		insert.append(")");
		insert.append(" VALUES ");
		insert.append(" (");
		insert.append(String.join(",", placeholderList));
		insert.append(") ");
		return insert.toString();
	}

	public static String getInsertSql(String table, String... columns) {
		Insert insert = new Insert();
//		Table t = new Table(table);
//		insert.setTable(t);
//		insert.setColumns((ExpressionList<Column>) Arrays.stream(columns).map(Column::new).collect(Collectors.toList()));
//		insert.getSetOperationList(Arrays.stream(columns)
//				.map(col -> new HexValue("#{" + CamelUtil.camelize(col) + "}")).collect(Collectors.toList()));
		return insert.toString();
	}

	public static String getUpdateSql(String table, CbExp dbExp, Map<String, Object> params, String... columns) {
		Update update = new Update();
		Table t = new Table(table);
		update.setTable(t);
		update.setColumns(Arrays.stream(columns).map(col -> new Column(t, col)).collect(Collectors.toList()));

		update.setExpressions(Arrays.stream(columns)
				.map(col -> new HexValue("#{" + CamelUtil.camelize(col) + "}")).collect(Collectors.toList()));
		return update.toString();
	}

	public static String getBatchUpdateSql(String table, String[] updateCols, String[] pkCols) {
		StringBuilder update = new StringBuilder();
		update.append("UPDATE ").append(table).append(" SET ");
		for (int i = 0; i < updateCols.length; i++) {
			update.append(CamelUtil.decamelize(updateCols[i])).append("=?");
			if (i < updateCols.length - 1) {
				update.append(",");
			}
		}

		update.append(" WHERE ");
		for (int i = 0; i < pkCols.length; i++) {
			update.append(CamelUtil.decamelize(pkCols[i])).append("=?");
			if (i < pkCols.length - 1) {
				update.append(",");
			}
		}
		return update.toString();
	}

	public static String getSelectSql(String table, String... columns) {
		return getSelectSql(table, null, null, columns);
	}

	public static String getSelectSql(String table, CbExp dbExp, Map<String, Object> params, String... columns) {
		Select select = null;
		try {
			if (columns == null||columns.length<1) {
				select = SelectUtils.buildSelectFromTable(new Table(table));
			} else {
				select = SelectUtils.buildSelectFromTableAndExpressions(new Table(table), columns);
			}
		} catch (JSQLParserException e) {
			throw new ChuangkeException(e);
		}

		if (dbExp == null
				|| (CollectionUtils.isEmpty(dbExp.getCbChildren()) && CollectionUtils.isEmpty(dbExp.getQcChildren()))) {
			return select.toString();
		}

		StringBuilder sql = new StringBuilder();
		sql.append(select.toString());
		sql.append(" where ").append(parseCbExp(dbExp, params));

		return sql.toString();
	}

	public static String getDeleteSql(String table) {
		return getDeleteSql(table, null, null);
	}

	public static String getDeleteSql(String table, CbExp dbExp, Map<String, Object> params) {
		Delete delete = new Delete();
		delete.setTable(new Table(table));
		if (dbExp == null
				|| (CollectionUtils.isEmpty(dbExp.getCbChildren()) && CollectionUtils.isEmpty(dbExp.getQcChildren()))) {
			return delete.toString();
		}

		StringBuilder sql = new StringBuilder();
		sql.append(delete);
		sql.append(" where ").append(parseCbExp(dbExp, params));

		return sql.toString();
	}

	public static String parseCbExp(CbExp dbExp, Map<String, Object> params) {
		if (CollectionUtils.isEmpty(dbExp.getCbChildren()) && CollectionUtils.isEmpty(dbExp.getQcChildren())) {
			return "";
		}
		if (CollectionUtils.isEmpty(dbExp.getCbChildren()) && dbExp.getQcChildren().size() == 1) {
			return parseQcExp(dbExp.getQcChildren().get(0), params);
		}

		CbExpOperatorEnum ceoe = CbExpOperatorEnum.getEnum(dbExp.getOperator());
		if (ceoe == null) {
			throw new ChuangkeException(String.format("系统不支持关系符:%s", dbExp.getOperator()));
		}

		StringBuilder sb = new StringBuilder();

		if (!CollectionUtils.isEmpty(dbExp.getQcChildren())) {
			for (int i = 0; i < dbExp.getQcChildren().size(); i++) {
				QcExp qcExp = dbExp.getQcChildren().get(i);
				if (i == 0) {
					sb.append("(");
				}
				sb.append(parseQcExp(qcExp, params));
				if (i < dbExp.getQcChildren().size() - 1) {
					sb.append(" ").append(ceoe.getDesc()).append(" ");
				}
				if (i == dbExp.getQcChildren().size() - 1) {
					sb.append(")");
				}
			}
		}

		if (!CollectionUtils.isEmpty(dbExp.getCbChildren())) {
			if (!StrUtil.isBlank(sb.toString())) {
				sb.append(ceoe.getDesc());
			}

			for (int i = 0; i < dbExp.getCbChildren().size(); i++) {
				if (i == 0) {
					sb.append("(");
				}
				sb.append(parseCbExp(dbExp.getCbChildren().get(i), params));
				if (i < dbExp.getCbChildren().size() - 1) {
					sb.append(" ").append(ceoe.getDesc()).append(" ");
				}
				if (i == dbExp.getCbChildren().size() - 1) {
					sb.append(")");
				}
			}
		}

		return sb.toString();
	}

	private static String parseQcExp(QcExp qcExp, Map<String, Object> params) {
		params.put(qcExp.getKey(), qcExp.getValue());

		QcExpOperatorEnum qeoe = QcExpOperatorEnum.getEnum(qcExp.getRelation());
		if (qeoe == null) {
			throw new ChuangkeException(String.format("系统不支持关系符:%s", qcExp.getRelation()));
		}

		StringBuilder sb = new StringBuilder();
		sb.append(CamelUtil.decamelize(qcExp.getKey()));
		sb.append(" ").append(qeoe.getDesc()).append(" ");
		sb.append("#{").append(qcExp.getKey()).append("}");
		return sb.toString();
	}

	public static String getDeleteSql() {
		return null;
	}
}
